<?php
    require('config.php');
    /**
     * 将数据库表生成sdf格式 required php version > 5.0
     *
     * @author   alone <phperlon@gmail.com>
     * @access   public
     * @param    
     * @return   
     * @todo     
     */
    class extract_sdf
    {
        protected $link;        //数据库链接资源
        private $alias;         //表别名
        private $spaceNum = 4;  //空格数目
        private $app;           //app名称
        private $prefix = PREFIX;        //表前缀

        public function __construct($ident)
        {
            $this->key = $ident;
            if (!is_dir($this->key)) {
                mkdir($this->key,0777,true);
            }
        }

        public function connect()
        {
            global $db;
            $con = mysql_connect($db[$this->key]['host'],$db[$this->key]['user'],$db[$this->key]['passwd']) or die(mysql_errno().': '.mysql_error());
            $charset = $db[$this->key]['charset']?$db[$this->key]['charset']:'UTF8';
            mysql_select_db($db[$this->key]['name'],$con) or die(mysql_errno().': '.mysql_error());
            mysql_query('SET NAMES '.$charset,$con);
            return $con;
            
        }

        public function setAlias($alias)
        {
            $this->alias = $alias;
        }

        public function setSpaceNum($num)
        {
            $this->spaceNum = $num;
        }

        public function setApp($app)
        {
            $this->app = $app;
            $this->prefix = PREFIX.$this->app.'_';
        }

        public function genStructure($table,$link=null)
        {
            if (!$link) {
                $this->link = $this->connect();
            }
            /* 提取列结构 */
            $res = mysql_query('SHOW FULL COLUMNS FROM '.$table,$this->link);
            $structure = array();
            while ($row = mysql_fetch_assoc($res)) {
                $col = $this->_filterCol($row);
                $structure['col'][] = $this->_filterCol($row);
            }
            //$this->genColumnSchema($structure['col'],$table);
            /* 提取索引 */
            $resIndex = mysql_query('SHOW INDEX FROM '.$table,$this->link);
            while ($row = mysql_fetch_assoc($resIndex)) {
                $this->_filterIndex($row,$structure['index']);
            }
            $this->genTableSchema($structure,$table);
            //$this->genIndexSchema($structure['index'],$table);
        }

        private function _filterCol($row)
        {
            $col = array(
                'field' => $row['Field'],
                'required' => 0,
            );
            if ($row['Null'] == 'NO') {
                $col['required'] = 1;
            }
            if ($row['Key'] == 'PRI') {
                $col['pkey'] = true;
            }
            if ($row['Default'] !== NULL && $row['Default'] !== '') {
                if (strstr($row['Type'],'int')) {
                    $col['default'] = intval($row['Default']);
                } else {
                    $col['default'] = $row['Default'];
                }
            }
            if ($row['Extra']) {
                $col['extra'] = $row['Extra'];
            }
            $col['comment'] = $row['Comment']?$row['Comment']:$row['Field'];
            $col['label'] = $col['comment'];
            if (strstr($row['Type'],'date')) {
                    $col['type'] = 'time';
                    if (isset($col['default'])) {
                        $col['default'] = 0;
                    }
                } elseif(strstr($row['Type'],'decimal')) {
                    $col['type'] = 'money';
                } elseif(strstr($row['Type'],'text')) {
                    $col['type'] = 'html';
                } elseif(strstr($row['Type'],'mediumint')) {
                    $col['type'] = 'number';
                } elseif(strstr($row['Type'],'longtext')) {
                    $col['type'] = 'serialize';
                } elseif(strstr($row['Type'],'enum')) {
                    switch ($row['Type']) {
                        case "enum('0','1')" :
                        case "enum('1','0')" :
                            $col['type'] = 'intbool';
                            break;
                        case "enum('true','false')" :
                        case "enum('false','true')" :
                            $col['type'] = 'bool';
                            break;
                        default :
                            $type = trim(substr($row['Type'],5,strlen($row['Type'])-6));
                            $type = str_replace("'","",$type);
                            $aType = explode(',',$type);
                            $col['type'] = $aType;
                    }
                } else {
                    $col['type'] = $row['Type'];
                }
                return $col;
        }

        private function _filterIndex($row, &$aIndex)
        {
            if ($row['Key_name'] != 'PRIMARY') {
                $aIndex[$row['Key_name']]['columns'][] = $row['Column_name'];
                if (!$row['Non_unique']) {
                    $aIndex[$row['Key_name']]['unique'] = true;
                }
            }
        }

        private function genTableSchema($structure,$table)
        {
            if (strpos($table,$this->prefix) === 0) {
                $db_table = substr($table,strlen($this->prefix));
            }
            $file = $db_table.'.php';
            $head = "<?php\n\$db['".$db_table."'] = array(\n";
            file_put_contents($this->key.'/'.$file,$head,FILE_APPEND);
            $this->genColumnSchema($structure['col'],$file,$table);
            if ($structure['index']) {
                $this->genIndexSchema($structure['index'],$file,$table);
            }
            $this->appendTableInfo($file,$table);
            $foot = ")\n?>";
            file_put_contents($this->key.'/'.$file,$foot,FILE_APPEND);
        }

        private function genColumnSchema($columns,$file,$table)
        {
            $colHead = $this->spaceRepeat()."'columns'=>array(\n";
            file_put_contents($this->key.'/'.$file,$colHead,FILE_APPEND);
            foreach ($columns as $k => $v) {
                $content = array();
                $content[] = $this->spaceRepeat(2)."'{$v['field']}'=>array(";
                if (!is_array($v['type'])) {
                    $content[] = $this->spaceRepeat(3)."'type'=>'{$v['type']}',";
                } else {
                    $str = array();
                    foreach ($v['type'] as $var) {
                        $str[] = "'{$var}'=>app::get('{$this->app}')->_('{$var}')";
                    }
                    $type = implode(',',$str);
                    $content[] = $this->spaceRepeat(3)."'type'=>array({$type}),";
                }
                if ($v['required']) {
                    $content[] = $this->spaceRepeat(3)."'required'=>true,";
                } else {
                    $content[] = $this->spaceRepeat(3)."'required'=>false,";
                }
                if (isset($v['pkey'])) {
                    $content[] = $this->spaceRepeat(3)."'pkey'=>true,";
                }
                if (isset($v['default'])) {
                    $content[] = $this->spaceRepeat(3)."'default'=>'{$v['default']}',";
                }
                $content[] = $this->spaceRepeat(3)."'label'=>app::get('{$this->app}')->_('{$v['label']}'),";
                $content[] = $this->spaceRepeat(3)."'comment'=>app::get('{$this->app}')->_('{$v['comment']}'),";
                $content[] = $this->spaceRepeat(3)."'width'=>110,";
                $content[] = $this->spaceRepeat(3)."'editable'=>false,";
                $content[] = $this->spaceRepeat(3)."'searchtype'=>'has',";
                $content[] = $this->spaceRepeat(3)."'filtertype'=>'normal',";
                $content[] = $this->spaceRepeat(3)."'filterdefault'=>true,";
                $content[] = $this->spaceRepeat(3)."'in_list'=>true,";
                $content[] = $this->spaceRepeat(3)."'default_in_list'=>true,";
                $content[] = $this->spaceRepeat(2)."),";
                file_put_contents($this->key.'/'.$file,implode("\n",$content)."\r\n",FILE_APPEND);
            }
            $colFoot = $this->spaceRepeat()."),\n";
            file_put_contents($this->key.'/'.$file,$colFoot,FILE_APPEND);
        }

        private function genIndexSchema($index,$file,$table)
        {
            $indexHead = $this->spaceRepeat()."'index'=>array(\n";
            file_put_contents($this->key.'/'.$file,$indexHead,FILE_APPEND);
            foreach ($index as $name =>$col) {
                $content = array();
                if (isset($col['unique']) && $col['unique']) {
                    $content[] = $this->spaceRepeat(2)."'{$name}'=>array('columns'=>array('".implode("','",$col['columns'])."'),'prefix'=>'UNIQUE',),";
                } else {
                    $content[] = $this->spaceRepeat(2)."'{$name}'=>array('columns'=>array('".implode("','",$col['columns'])."'),),";
                }
                file_put_contents($this->key.'/'.$file,implode("\n",$content)."\r\n",FILE_APPEND);
            }
            $indexFoot = $this->spaceRepeat()."),\n";
            file_put_contents($this->key.'/'.$file,$indexFoot,FILE_APPEND);
        }

        private function appendTableInfo($file,$table)
        {
            $sql = 'SHOW TABLE STATUS LIKE '.$this->quote($table);
            $res = mysql_query($sql,$this->link);
            if ($row = mysql_fetch_assoc($res)) {
                $comment = $row['Comment']?$row['Comment']:$row['Name'];
                $engine = $row['Engine'];
                $content[] = $this->spaceRepeat()."'comment'=>'{$comment}',";
                $content[] = $this->spaceRepeat()."'engine'=>'{$engine}',";
                file_put_contents($this->key.'/'.$file,implode("\n",$content)."\n",FILE_APPEND);
            }
        }

        public function spaceRepeat($multiple=1)
        {
            return str_repeat(' ',$multiple*$this->spaceNum);
        }

        public function quote($str)
        {
            return '"'.$str.'"';
        }

        public function backQuote($str)
        {
            return '`'.$str.'`';
        }

        public function genSdf($table='',$deep=false,$aFiled=array('*'),$aFilter=array(),$ignoreField=array())
        {
            $this->link = $this->connect();
            if (is_string($table) && $table) {
                $table = trim($table);
                $this->getSingle($table,$deep);
            } elseif(is_array($table)) {
                foreach ($table as $v) {
                    $this->getSingle($v,$deep);
                }
            }elseif(!$table) {
                $res = mysql_query('SHOW TABLES');
                while ($row = mysql_fetch_row($res)) {
                    $this->getSingle($row[0],$deep);
                }
            }
        }

        public function getSingle($table,$deep)
        {
            $selectFiled = $this->_filterField($table);
            $sql = 'SELECT '.implode(',',$selectFiled).' FROM '.$table.' WHERE '.$this->_filter($aFilter);
            if (strpos($table,$this->prefix) === 0) {
                $db_table = substr($table,strlen($this->prefix));
            }
            $res = mysql_query($sql,$this->link);
            if ($res) {
                while ($row = mysql_fetch_assoc($res)) {
                    file_put_contents($this->key.'/'.$this->app.'.'.$db_table.'.sdf',serialize($row)."\r\n",FILE_APPEND);
                }
            } else {
                error_log(print_r($sql.'--'.$table."\r\n",true),3,__FILE__.'.'.__FUNCTION__.'.log');
            }
            if ($deep) {
                $this->genStructure($table,$this->link);
            }
        }

        private function _filterField($table,$aFiled=array('*'),$ignoreField=array())
        {
            $selectFiled = array();
            $res = mysql_query('SHOW FULL COLUMNS FROM '.$table,$this->link);
            while ($row = mysql_fetch_assoc($res)) {
                if (!in_array($row['Field'],$ignoreField)) {
                    $field = $this->backQuote($row['Field']);
                    if (strstr($row['Type'],'date')) {
                        $selectFiled[] = 'unix_timestamp('.$field.') AS '.$field;
                    } else {
                        $selectFiled[] = $field;
                    }
                }
            }
            return $selectFiled;
        }

        public function _filter($aFilter,$alias='')
        {
            if (empty($aFilter)) {
                return ' 1 ';
            }
            if (is_array($aFilter)) {
                $aWhere = array();
                foreach ($aFilter as $field => $var) {
                    $aWhere[] = $this->backQuote($field).'='.$this->quote($var).'';
                }
                return implode(' AND ',$aWhere);
            }
        }

        public function array_walk_ext(&$arr,$func='trim',$sep='')
        {
            if (is_array($arr)) {
                foreach ($arr as $k => $v) {
                    $arr[$k] = $sep?call_user_func($func,$v,$sep):call_user_func($func,$v);
                }
            }
        }
    }

    $obj = new extract_sdf('shopex_csm');
    $obj->setApp('csm');
    //$table = array('sdb_csm_action','sdb_csm_attribute','sdb_csm_dimension','sdb_csm_lic','sdb_csm_product','sdb_csm_report','sdb_csm_target');
    $obj->genSdf($table='',true);

?>